package com.wwwc.index.ejb.category;

import java.sql.*;
import javax.sql.*;
import java.util.*;
import javax.ejb.*;
import javax.naming.*;
import java.rmi.RemoteException;

import com.wwwc.index.servlet.CategoryDetails;

public class CategoryBean implements SessionBean {
    private CategoryDetails categoryDetails;
    private Connection con;

    public void ejbCreate() throws CreateException {}
    public void ejbRemove() {}
    public void ejbActivate() {}
    public void ejbPassivate() {}
    public void setSessionContext(SessionContext sc) {}

    public int categoryTableExists() {
	int found = 0;
        try {  
	    makeConnection();
	    String query = "SELECT TABLE_NAME FROM SYSTEM_TABLES WHERE TABLE_NAME='WWWC_CATEGORY';";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	    while (rs.next()) {
		if((rs.getString(1)).equals("WWWC_CATEGORY")) {
		    found = 1;
		    break;
		}
	    }
	    rs.close();
	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    found = -1;
	    System.out.println("CategoryBean:tableExists:Error:"+e.getMessage());
        } 
	return found;
    } 

    public void dropCategoryTable() {
	PreparedStatement prepStmt = null;
        try {  
	    String query = "DROP TABLE WWWC_CATEGORY;";
	    makeConnection();
	    prepStmt = con.prepareStatement(query);
	    prepStmt.executeUpdate();
	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    System.out.println("CategoryBean:dropCategoryTable:Error:"+e);
        } 
    } 

    public int createCategoryTable() {
	PreparedStatement prepStmt = null;
        try {  
	    StringBuffer sbf = new StringBuffer();
	    sbf.append("CREATE TABLE WWWC_CATEGORY(");
	    sbf.append("CATEGORY_ID INTEGER PRIMARY KEY,");
	    sbf.append("CATEGORY_NAME VARCHAR(100),");
	    sbf.append("DIRECTORY_TABLE_NAME VARCHAR_IGNORECASE(100),");
	    sbf.append("ACCESS_LEVEL INT");
	    sbf.append(");");

	    makeConnection();
	    prepStmt = con.prepareStatement(sbf.toString());
	    ResultSet rs = prepStmt.executeQuery();

	    rs.close();
	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    System.out.println("CategoryBean:createCategoryTable:Error:"+e);
	    return 0;
        } 
	return 1;
    } 

    public int insertCategoryDefaultValue () {
	PreparedStatement prepStmt = null;
        try {  
	    StringBuffer sbf = new StringBuffer();
	    sbf.append("INSERT INTO WWWC_CATEGORY(");
	    sbf.append("CATEGORY_ID,");
	    sbf.append("CATEGORY_NAME,");
	    sbf.append("DIRECTORY_TABLE_NAME,");
	    sbf.append("ACCESS_LEVEL");
	    sbf.append(") VALUES (?, ?, ?, ?);");

	    makeConnection();
	    prepStmt = con.prepareStatement(sbf.toString());

	    prepStmt.setInt(1, 10);
	    prepStmt.setString(2, "Forums");
	    prepStmt.setString(3, "WWWC_DIRECTORY_FORUMS");
	    prepStmt.setInt(4, 0);
	    prepStmt.executeUpdate();

	    prepStmt.setInt(1, 20);
	    prepStmt.setString(2, "Blogs");
	    prepStmt.setString(3, "WWWC_DIRECTORY_BLOGS");
	    prepStmt.setInt(4, 0);
	    prepStmt.executeUpdate();

	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    System.out.println("CategoryBean:createCategoryTable:Error:"+e);
	    return 0;
        } 
	return 1;
    } 

    public int addNewCategory (String cid, String cname, String level) {
	PreparedStatement prepStmt = null;
        try {  
	    StringBuffer sbf = new StringBuffer();
	    sbf.append("INSERT INTO WWWC_CATEGORY(");
	    sbf.append("CATEGORY_ID,");
	    sbf.append("CATEGORY_NAME,");
	    sbf.append("DIRECTORY_TABLE_NAME,");
	    sbf.append("ACCESS_LEVEL");
	    sbf.append(") VALUES (?, ?, ?, ?);");

	    makeConnection();
	    prepStmt = con.prepareStatement(sbf.toString());
	    prepStmt.setString(1, cid);
	    prepStmt.setString(2, cname);
	    prepStmt.setString(3, "WWWC_DIRECTORY_"+cname.toUpperCase());
	    prepStmt.setString(4, level);
	    prepStmt.executeUpdate();

	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    System.out.println("CategoryBean:addNewCategory:Error:"+e);
	    return 0;
        } 
	return 1;
    } 

    public int updateCategory (String cid, String cname, String level) {
	PreparedStatement prepStmt = null;
        try {  
	    String query = "UPDATE WWWC_CATEGORY SET CATEGORY_NAME=?, ACCESS_LEVEL=? WHERE CATEGORY_ID=?;";
	    makeConnection();
	    prepStmt = con.prepareStatement(query);
	    prepStmt.setString(1, cname);
	    prepStmt.setString(2, level);
	    prepStmt.setString(3, cid);
	    prepStmt.executeUpdate();

	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    System.out.println("CategoryBean:updateCategory:Error:"+e);
	    return 0;
        } 
	return 1;
    } 

    public int setDefaultCategory (String cid) {
	if(cid == null || cid.length()==0) {
	    return 0;
	}
	
	try {
	    makeConnection();
	    String query = "UPDATE WWWC_CATEGORY SET CATEGORY_ID=? WHERE CATEGORY_ID=?;";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    prepStmt.setInt(1, -1);
	    prepStmt.setInt(2, 10);
	    prepStmt.executeUpdate();

	    prepStmt.setInt(1, 10);
	    prepStmt.setString(2, cid);
	    prepStmt.executeUpdate();

	    prepStmt.setString(1, cid);
	    prepStmt.setInt(2, -1);
	    prepStmt.executeUpdate();

	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
             System.out.println("EJB:CategoryBean:setDefaultCategory:Error:" + e.getMessage());
	     return 0;
        } 
	return 1;
    }

    public int deleteCategory (String cid) {
	PreparedStatement prepStmt = null;
        try {  
	    String query = "DELETE FROM WWWC_CATEGORY WHERE CATEGORY_ID="+cid+";";
	    makeConnection();
	    prepStmt = con.prepareStatement(query);
	    prepStmt.executeUpdate();

	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    System.out.println("CategoryBean:deleteCategory:Error:"+e);
	    return 0;
        } 
	return 1;
    } 

    public ArrayList getCategoryList(int level) {
	ArrayList a = null;
	CategoryDetails cd = null;

        try {
	    makeConnection();
	    String query = "SELECT CATEGORY_ID, CATEGORY_NAME, DIRECTORY_TABLE_NAME, ACCESS_LEVEL FROM WWWC_CATEGORY WHERE "+
		"ACCESS_LEVEL <="+level+" ORDER BY CATEGORY_ID;";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();

	    a = new ArrayList();

	    while (rs.next()) {
		cd = new CategoryDetails();
		cd.setCategoryId(rs.getInt(1));
		cd.setCategoryName(rs.getString(2));
		cd.setDirectoryTableName(rs.getString(3));
		cd.setCategoryLevel(rs.getInt(4));
		a.add(cd);
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
             System.out.println("EJB:CategoryBean:getCategoryList:error:" + e.getMessage());
        } 
        return a;
    } 

    ////////////////////////////////////////////////////////////////////
    // Database Routines
    ////////////////////////////////////////////////////////////////////

    private void makeConnection() {
        try {
	    InitialContext ic = new InitialContext();
            DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/DefaultDS");
            con =  ds.getConnection();
        } catch (Exception e) {
	    releaseConnection();
	    System.out.println("EJB:CategoryBean: makeConnection error:" + e.getMessage());
        }
    }
  
    private void releaseConnection() {
        try {
            con.close();
        } catch (SQLException e) {
	    System.out.println("CategoryBean: Reaease connect error: " + e.getMessage());
        }
    }
}
